package com.bolt.report.business.modules.datasource.service;

import com.bolt.common.db.DbHelper;
import com.bolt.common.utils.StrUtil;
import com.bolt.convention.data.PageInfo;
import com.bolt.convention.exception.ServiceException;
import com.bolt.report.business.modules.datasource.dto.DataSetAnalyzeParamDTO;
import com.bolt.report.business.modules.datasource.dto.ReportDataSourceDTO;
import com.bolt.report.business.modules.datasource.dto.converter.ReportDataSourceConverter;
import com.bolt.report.business.modules.datasource.entity.ReportDataSourceEntity;
import com.bolt.report.business.modules.datasource.repository.ReportDataSourceRepository;
import com.bolt.report.business.modules.param.dto.converter.ReportDbParamConverter;
import com.bolt.report.definition.JdbcDataSourceDefinition;
import com.bolt.report.definition.ParameterDefinition;
import com.bolt.report.definition.ParameterType;
import com.bolt.report.engine.ObjectFactory;
import com.bolt.report.engine.pool.JdbcConnectionPool;
import com.bolt.report.exception.ReportBizExceptionEnum;
import com.bolt.support.base.service.BaseService;
import com.bolt.support.codegenerator.db.*;
import com.bolt.support.codegenerator.dto.TableInfo;
import com.bolt.support.codegenerator.model.Table;
import com.bolt.support.spring.jpa.query.PropertiesSqlParameterSource;
import com.bolt.support.spring.jpa.repository.BasicJpaRepository;
import com.bolt.support.sqlconfig.base.SqlStatement;
import com.bolt.support.sqlconfig.base.SqlText;
import com.bolt.support.sqlconfig.dialect.PagingDialect;
import com.bolt.support.sqlconfig.dialect.PagingDialectFactory;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Auto Generate Javadoc
 *
 * @author 日期:2021-11-17 14:15:55
 **/
@Service
@Transactional(readOnly = true, rollbackFor = Exception.class)
@RequiredArgsConstructor
@Slf4j
public class ReportDataSourceService extends BaseService<ReportDataSourceEntity, String> {

    private final ReportDataSourceRepository reportDataSourceRepository;

    @Resource(name = "druidJdbcPool")
    private JdbcConnectionPool connectionPool;

    @Override
    protected BasicJpaRepository<ReportDataSourceEntity, String> getRepository() {
        return reportDataSourceRepository;
    }

    @Transactional(rollbackFor = Exception.class)
    public void saveOrUpdate(ReportDataSourceDTO dto) {
        ReportDataSourceEntity entity = ReportDataSourceConverter.toEntity(dto);
        connectionPool.removeJdbcConnectionPool(entity.getCode());
        save(entity);
    }

    public ReportDataSourceDTO findDataSource(String dataSourceId) {
        ReportDataSourceEntity dataSourceEntity = findOne(dataSourceId)
                .orElseThrow(() -> new ServiceException(ReportBizExceptionEnum.DATA_NOT_FOUND));
        return ReportDataSourceConverter.toDTO(dataSourceEntity);
    }

    public List<ReportDataSourceEntity> queryAllDataSource() {
        return this.findAll();
    }

    public JdbcDataSourceDefinition findDataSourceDefinition(String dataSourceId) {
        ReportDataSourceDTO reportDataSourceDTO = findDataSource(dataSourceId);
        return ReportDataSourceConverter.toDataSourceDefinition(reportDataSourceDTO);
    }


    public List<Map<String, Object>> loadTableData(DataSetAnalyzeParamDTO sqlAnalyzeParam, Pageable pageable) {

        ReportDataSourceDTO sourceDTO = findDataSource(sqlAnalyzeParam.getDataSourceId());
        JdbcDataSourceDefinition dataSourceDefinition = ReportDataSourceConverter.toDataSourceDefinition(sourceDTO);
        Connection pooledConnection = null;
        try {
            pooledConnection = connectionPool.getPooledConnection(dataSourceDefinition);

            List<ParameterDefinition> parameterDefinitions = ReportDbParamConverter.toDefinition(sqlAnalyzeParam.getParams());
            Map<String, Object> params = convertSqlQueryParam(parameterDefinitions);

            SingleConnectionDataSource datasource = new SingleConnectionDataSource(pooledConnection, false);
            SqlStatement queryStatement = ObjectFactory.parseSqlText(sqlAnalyzeParam.getDbDynSql());
            PagingDialect pagingDialect = PagingDialectFactory.getDialect(sourceDTO.getDbUrl());
            SqlText sqlText = queryStatement.getSqlText(params);
            String pageSql = pagingDialect.getLimitString(sqlText.getSql(), pageable.getOffset(), pageable.getPageSize());
            NamedParameterJdbcTemplate query = new NamedParameterJdbcTemplate(datasource);
            SqlParameterSource parameterSource = new PropertiesSqlParameterSource(sqlText.getParameterValues());
            return query.queryForList(pageSql, parameterSource);
        } catch (Exception throwable) {
            log.error("error", throwable);
            throw new ServiceException(ReportBizExceptionEnum.EXECUTE_SQL_ERROR, throwable);
        } finally {
            JdbcUtils.closeConnection(pooledConnection);
        }
    }

    public Table loadTable(String dataSourceId, String tableName) {
        TableFactory tableFactory = getTableFactory(dataSourceId);
        try {
            return tableFactory.getTable(tableName);
        } catch (Exception ex) {
            log.error("load table filed error", ex);
            throw new ServiceException(ReportBizExceptionEnum.EXECUTE_SQL_ERROR, ex);
        }

    }


    /**
     * 测试数据源连结
     *
     * @param dto
     * @return
     */
    public Boolean testConnection(ReportDataSourceDTO dto) {
        switch (dto.getType()) {
            case SQL:
                testRelationalDb(dto);
                break;
            default:
                throw new ServiceException(ReportBizExceptionEnum.DATA_SOURCE_TYPE_NOT_SUPPORT);
        }
        return true;
    }


    /**
     * 关系型数据库 测试连接
     *
     * @param dto
     */
    public void testRelationalDb(ReportDataSourceDTO dto) {
        try {
            JdbcDataSourceDefinition dataSourceDefinition = ReportDataSourceConverter.toDataSourceDefinition(dto);
            connectionPool.testConnection(dataSourceDefinition);
        } catch (SQLException e) {
            log.error("error", e);
            if (e.getCause() instanceof ClassNotFoundException) {
                throw new ServiceException(ReportBizExceptionEnum.CLASS_NOT_FOUND, e);
            } else {
                throw new ServiceException(ReportBizExceptionEnum.DATA_SOURCE_CONNECTION_FAILED, e);
            }
        }
    }

    public PageInfo<TableInfo> buildDatabaseTables(String dataSourceId, String tableName, Pageable pageable) {
        TableFactory tableFactory = getTableFactory(dataSourceId);
            try {
            PageInfo<TableInfo> pageInfo = tableFactory.getTables(tableName, pageable.getPageNumber(), pageable.getPageSize());
            return pageInfo;
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }

    }

    public TableFactory getTableFactory(String dataSourceId) {
        ReportDataSourceEntity dataSource = findOne(dataSourceId).orElseThrow(() -> new ServiceException(ReportBizExceptionEnum.DATA_NOT_FOUND));
        TableFactory tableFactory = null;
        if (DbHelper.dbTypeIsMySQL(dataSource.getDbUrl())) {
            tableFactory = new MysqlTableFactory(dataSource.getDbUrl(), dataSource.getDbUsername(), dataSource.getDbPassword(), dataSource.getDbDriver());
        } else if (DbHelper.dbTypeIsOracle(dataSource.getDbUrl())) {
            tableFactory = new OracleTableFactory(dataSource.getDbUrl(), dataSource.getDbUsername(), dataSource.getDbPassword(), dataSource.getDbDriver());
        } else if (DbHelper.dbTypeIsPostgre(dataSource.getDbUrl())) {
            tableFactory = new PostgreSqlTableFactory(dataSource.getDbUrl(), dataSource.getDbUsername(), dataSource.getDbPassword(), dataSource.getDbDriver());
        } else if (DbHelper.dbTypeIsSQLServer(dataSource.getDbUrl())) {
            tableFactory = new SqlServerTableFactory(dataSource.getDbUrl(), dataSource.getDbUsername(), dataSource.getDbPassword(), dataSource.getDbDriver());
        }
        return tableFactory;
    }



    private Map<String, Object> convertSqlQueryParam(List<ParameterDefinition> definitions) {
        Map<String, Object> param = new HashMap<>();
        for (ParameterDefinition definition : definitions) {
            String paramName = definition.getName();
            ParameterType paramType = definition.getParameterType();
            if (StrUtil.isNotBlank(definition.getDefaultValue())) {
                Object value = ObjectFactory.convertParameter(definition.getDefaultValue(), paramType);
                param.put(paramName, value);
            }

        }
        return param;
    }

}
